|
|
|
在ET表格中，對于班級成績冊的各科求總分、平均分以及排名次等，都可算是輕松的任務(wù)。但是，對于多年級多班級混合編排的班級，如果想再類似的操作，那又該如何進行呢?
圖1
圖1所示為不同學校不同專業(yè)混編的示意成績冊。我們需要計算各學校各專業(yè)各科目的總分和平均分，并對各學生在各自學校各自專業(yè)內(nèi)排定名次。這項看起來艱巨復雜的任務(wù)，在ET表格中正確使用SUMPRODUCT函數(shù)，那么完成起來其實也并不算怎么困難。咱們只需如此操作即可：
一、多條件求和
比如我們需要計算工業(yè)職專機電專業(yè)所有學生的語文成績總分。分析表格可以看到：學校名稱在B2:B16單元格區(qū)域，專業(yè)名稱在C2:C16區(qū)域，語文成績則分布在D2:D16區(qū)域。在合適的單元格輸入公式“=SUMPRODUCT((B2:B16="工業(yè)職專")*(C2:C16="機電"),D2:D16)”，回車后可以得到結(jié)果“228”了。看出來了吧?公式中的前兩個小括號內(nèi)就是需要滿足的條件，而“D2:D16”就表示需要求和的區(qū)域。如果有更多的條件，那就再在前面加小括號就可以了。要注意的是小括號中間用“*”連接。
至于我們希望的各學校各專業(yè)各科目的總分，那就得設(shè)計如圖2所示表格來解決了。
圖2
先建好空白表格，如圖2所示錄入學校名稱和專業(yè)名稱。在N2單元格輸入公式 “=SUMPRODUCT(($B$2:$B$16=$L3)*($C$2:$C$16=$M3),D$2:D$16)”，選中該單元格的填充句柄，向右和向下拖動復制公式至整個表格，那么各學校各專業(yè)各學科的總分就有了。不太難吧?
由于各學校的專業(yè)設(shè)置并不相同，所以某些的某些專業(yè)的各科總分計算為“0”。如果想追求盡善盡美，那么我們可以用IF函數(shù)，當結(jié)果為“0”時不顯示任何內(nèi)容，這樣可以使結(jié)果顯示盡量美觀一些。公式為“=IF(SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16)=0,"",SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16))”。
二、多條件求平均分
求和與求平均分只有一步之遙，那就是需要統(tǒng)計出各學校每個專業(yè)的人數(shù)。有了它，那么平均分就簡單多了。這同樣可以使用SUMPRODUCT函數(shù)來完成。
圖3
在如圖3所示的人數(shù)統(tǒng)計表的N18單元格輸入公式“=SUMPRODUCT(($B$2:$B$16=$L18)*($C$2:$C$16=$M18)*(D$2:D$16<>""))”，然后向右向下復制公式就可以了。
求平均分即拿總分除以人數(shù)。因此，如果人數(shù)為“0”，那么求平均分時就會出現(xiàn)問題。因此，在求平均分時，同樣需要用IF函數(shù)做一個判斷。借用前面的兩個表格，那么工業(yè)職專機電專業(yè)的語文平均分公式可寫為“=IF(ISERROR(N3/N18),"",N3/N18)”，其余的復制公式即可，如圖4所示。
圖4
公式中的ISERROR(N3/N18)作用判斷“N3/N18”結(jié)果是否會出現(xiàn)錯誤。
如果直接不借助人數(shù)統(tǒng)計表格直接求平均分，那么該公式可寫為“=IF(SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3)*(D$2:D$16<>""))=0,"",SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16)/SUMPRODUCT(($B$2:$B$16=$L3)*($C$2:$C$16=$M3)* (D$2:D$16<>"")))”。
三、多條件下的排名次
在不破壞原來數(shù)據(jù)表排序的情況下，為每位學生排定在本校本專業(yè)內(nèi)的名次，看起來很難，但是有了SUMPRODUCT就不一樣了。試想一下，借助于SUMPRODUCT函數(shù)，我們可以輕松統(tǒng)計出符合多個條件的人數(shù)。那么工業(yè)職專機電專業(yè)總分為“616”的同學的名次，不就是學校為“工業(yè)職專”、專業(yè)為“機電”、總分“>616”的人數(shù)再加上1嘛!
有了這個思路，公式就好寫了吧? J2單元格公式為“=SUMPRODUCT(($B$2:$B$16=B2)*($C$2:$C$16=C2)*($I$2:$I$16>I2))+1”，寫完后，向下復制公式就行了。結(jié)果如圖5所示。
圖5
|
|
發(fā)表留言請先登錄！
|